{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "cnx = mysql.connector.connect(user='root', password='root',\n",
    "                              host='127.0.0.1',\n",
    "                              database='usTreasury')\n",
    "\n",
    "TABLES = {}\n",
    "TABLES['usTreasury'] = (\n",
    "    \"CREATE TABLE `usTreasury` (\"\n",
    "    \"  `date` varchar(11) NOT NULL,\"\n",
    "    \"  `1 month` varchar(5) NOT NULL,\"\n",
    "    \"  `3 month` varchar(5) NOT NULL,\"\n",
    "    \"  `6 month` varchar(5) NOT NULL,\"\n",
    "    \"  `1 year` varchar(5) NOT NULL,\"\n",
    "    \"  `2 year` varchar(5) NOT NULL,\"\n",
    "    \"  `3 year` varchar(5) NOT NULL,\"\n",
    "    \"  `5 year` varchar(5) NOT NULL,\"\n",
    "    \"  `7 year` varchar(5) NOT NULL,\"\n",
    "    \"  `10 year` varchar(5) NOT NULL,\"\n",
    "    \"  `20 year` varchar(5) NOT NULL,\"\n",
    "    \"  `30 year` varchar(5) NOT NULL,\"\n",
    "    \"  PRIMARY KEY (`date`)\"\n",
    "    \") ENGINE=InnoDB\")\n",
    "CREATE TABLES \n",
    "cnx.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import MySQLdb\n",
    "db = MySQLdb.connect(\"localhost\",\"root\",\"root\",\"usTreasury\" )\n",
    "cursor = db.cursor()\n",
    "# cursor.execute('select * from usTreasury')\n",
    "# tablerows=cursor.fetchall()\n",
    "\n",
    "sql = \"\"\"CREATE TABLE usTreasury (\n",
    "         date  DATE NOT NULL,\n",
    "        1_month CHAR(5), \n",
    "        3_month CHAR(5),\n",
    "        6_month CHAR(5), \n",
    "        1_year CHAR(5), \n",
    "        2_year CHAR(5),\n",
    "        3_year CHAR(5), \n",
    "        5_year CHAR(5), \n",
    "        7_year CHAR(5), \n",
    "        10_year CHAR(5), \n",
    "        20_year CHAR(5), \n",
    "        30_year CHAR(5)\n",
    "         )\"\"\"\n",
    "cursor.execute(sql)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import MySQLdb\n",
    "\n",
    "\n",
    "db = MySQLdb.connect(\"localhost\",\"root\",\"root\",\"usTreasury\" )\n",
    "\n",
    "\n",
    "cursor = db.cursor()\n",
    "\n",
    "\n",
    "a = [u'12/31/14', u'0.03', u'0.04', u'0.12', u'0.25', u'0.67', u'1.10', u'1.65', u'1.97', u'2.17', u'2.47', u'2.75']\n",
    "\n",
    "sql = \"\"\"INSERT INTO usTreasury(date,\n",
    "         1_month, 3_month, 6_month, 1_year,2_year,3_year,5_year,7_year,10_year,20_year,30_year)\n",
    "         VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)\"\"\"\n",
    "try:\n",
    "   \n",
    "   cursor.execute(sql,a)\n",
    "   \n",
    "   db.commit()\n",
    "except:\n",
    "   \n",
    "   db.rollback()\n",
    "\n",
    "    \n",
    "sql = \"SELECT * from usTreasury where date = (SELECT max(date) FROM usTreasury)\"\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchone()\n",
    "print results\n",
    "\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# -------------------------------------------  \n",
    "# Python MySQLdb 循环插入execute与批量插入executemany性能分析  \n",
    "# 插入数据量：10000条  \n",
    "# 每条字段：username, salt, pwd  \n",
    "# Author : Lrg  \n",
    "# -------------------------------------------  \n",
    "# encoding = utf-8  \n",
    "import MySQLdb  \n",
    "import xlrd  \n",
    "import time  \n",
    "import sys  \n",
    "reload(sys)  \n",
    "sys.setdefaultencoding(\"utf-8\")  \n",
    "  \n",
    "# 从users.xls文件获取10000条用户数据  \n",
    "# 该文件由create_users.py生成  \n",
    "def get_table():  \n",
    "    FILE_NAME = 'users.xls'  \n",
    "    data = xlrd.open_workbook(FILE_NAME)  \n",
    "    table = data.sheets()[0]  \n",
    "    return table  \n",
    "  \n",
    "# 循环插入execute     \n",
    "def insert_by_loop(table):  \n",
    "    nrows = table.nrows  \n",
    "    for i in xrange(1,nrows):  \n",
    "        param=[]  \n",
    "        try:  \n",
    "            sql = 'INSERT INTO user values(%s,%s,%s)'  \n",
    "            # 第一列username，第二列salt，第三列pwd  \n",
    "            print 'Insert: ',table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value  \n",
    "            param = (table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value)  \n",
    "            # 单条插入  \n",
    "            cur.execute(sql, param)  \n",
    "            conn.commit()  \n",
    "        except Exception as e:  \n",
    "            print e  \n",
    "            conn.rollback()  \n",
    "    print '[insert_by_loop execute] total:',nrows-1  \n",
    "  \n",
    "# 批量插入executemany  \n",
    "def insert_by_many(table):  \n",
    "    nrows = table.nrows  \n",
    "    param=[]  \n",
    "    for i in xrange(1,nrows):  \n",
    "        # 第一列username，第二列salt，第三列pwd  \n",
    "        param.append([table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value])  \n",
    "    try:  \n",
    "        sql = 'INSERT INTO user values(%s,%s,%s)'  \n",
    "        # 批量插入  \n",
    "        cur.executemany(sql, param)  \n",
    "        conn.commit()  \n",
    "    except Exception as e:  \n",
    "        print e  \n",
    "        conn.rollback()   \n",
    "    print '[insert_by_many executemany] total:',nrows-1   \n",
    "  \n",
    "  \n",
    "# 连接数据库  \n",
    "conn = MySQLdb.connect(host=\"127.0.0.1\", port=3306, user=\"lrg\", passwd=\"lrg\", db=\"pythontest\")  \n",
    "cur = conn.cursor()  \n",
    "  \n",
    "# 新建数据库  \n",
    "cur.execute('DROP TABLE IF EXISTS user')  \n",
    "sql = \"\"\"CREATE TABLE user( \n",
    "        username CHAR(255) NOT NULL, \n",
    "        salt CHAR(255), \n",
    "        pwd CHAR(255) \n",
    "        )\"\"\"  \n",
    "cur.execute(sql)  \n",
    "  \n",
    "# 从excel文件获取数据  \n",
    "table = get_table()  \n",
    "  \n",
    "# 使用循环插入  \n",
    "start = time.clock()  \n",
    "insert_by_loop(table)  \n",
    "end = time.clock()  \n",
    "print '[insert_by_loop execute] Time Usage:',end-start  \n",
    "  \n",
    "# 使用批量插入  \n",
    "start = time.clock()  \n",
    "insert_by_many(table)  \n",
    "end = time.clock()  \n",
    "print '[insert_by_many executemany] Time Usage:',end-start  \n",
    "  \n",
    "# 释放数据连接  \n",
    "if cur:  \n",
    "    cur.close()  \n",
    "if conn:  \n",
    "    conn.close()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
